<?php

namespace app\service;

use app\exception\ModelEmptyException;
use app\model\Model;
use app\model\ModuleField;
use app\model\Module;
use app\model\RecycleBin;
use app\model\SubContent;
use app\scaffold\Table;
use think\facade\Db;

class ModuleFieldService {

    protected $fieldType;

    protected $tableName;

    /**
     *
     * @var \app\scaffold\Table
     */
    protected $table;

    public function __construct()
    {   
        $this->fieldType = config('moduleField');
        $this->table = new Table();
    }

    /**
     * 保存字段
     *
     * @param array $params
     * @return array
     * @throws ModelEmptyException
     * @throws \app\exception\ModelException
     */
    public function save(array $params)
    {
        $module = $this->getTableModule($params['module_id'],$params['seller_id']);
        $tableName = $module['database_table'];
        if(!$tableName){
            return dataReturn(50022,'模型不存在');
        }
        $flag = $this->hasColumn($tableName,$params['table_field']);
        if($flag){
            return dataReturn(50023,'字段已经存在');
        }
        $res = $this->uniqueValidate($params['module_id'],$params,false);
        if($res['code'] != 0){
            return $res;
        }
        if(!in_array($params['type'],$this->fieldType['dataTypes'])){
            return dataReturn(50024,'字段类型不存在');
        }
        $orderFlag = $this->hasOrderField($params['order'],$params['module_id']);
        if(!$orderFlag){
            $params['order'] = 'id';
        }
        $params = $this->setParams($params);
        $moduleField =  new ModuleField();
        Db::startTrans();
        try {
            $res = $this->hasOrder($params['order'],$params['module_id']);
            if(isset($res['id'])){
                $field_id = $res['id'];
                $moduleField->updateModuleField(['id' => $field_id,'seller_id' => $params['seller_id']],['order' => $params['table_field']]);
            }
            if($params['type'] == '关联字段'){
                $ass_res = $this->createRelation($tableName,$params);
                $params['settings']['local_table'] = str_replace(env('database.prefix'),'',$tableName);
                if(isset($ass_res['code'])) {
                    return $ass_res;
                }
            }else{
                $options = $this->getFieldOption($params);
                $this->addColumn($tableName,$params['table_field'],$params['db_type'],$options);
            }
            if(!empty($params['settings'])){
                $params['settings'] = json_encode($params['settings'],true);
            }
            $moduleFieldRes = $moduleField->addModuleField($params);
            Db::commit();
        } catch (\Exception $e) {
            $res = $this->hasColumn($tableName,$params['table_field']);
            if($res){
                $this->dropColumn($tableName,$params['table_field']);
            }
            Db::rollback();
            return dataReturn(50015,$e->getMessage());
        }

        return $moduleFieldRes;
    }

    /**
     * 完成参数配置
     * @param $params
     * @return mixed
     */
    public function setParams(&$params)
    {
        $fieldType = $this->fieldType;
        $params['db_type'] = $fieldType['dbTypeMaps'][$params['type']];
        $params['form_type'] = $fieldType['formTypeMaps'][$params['type']];
        if(!empty($params['attach_data'])){
            $params['attach_data'] = $this->dealWithComma($params['attach_data']);
        }
        if(!empty($params['form_validate'])){
            $validate = $params['form_validate'];
            $validate_rule = '';
            foreach($validate as $val){
                $validate_rule .= $fieldType['validateRules'][$val].'|';
            }
            if(!empty($params['length'])){
                $validate_rule .= 'length:0,' . $params['length'];
            }
            $params['validate_rule'] = trim($validate_rule,'|');
        }
        if($params['type'] == "关联字段"){
            if(empty($params['settings']['foreign_key'])){
                $params['settings']['foreign_key'] = $params['setting']['table'] .'_id';
            }
            if(empty($params['settings']['local_key'])){
                $params['settings']['local_key'] = 'id';
            }
        }
        return $params;
    }

    /**
     * 创建关联关系
     *
     * @param string $tableName
     * @param array $params
     */
    public function createRelation(string $tableName, array $params)
    {
        $prefix = env('database.prefix');
        $relationTable = $prefix.$params['settings']['table'];
        $localKey = $params['settings']['local_key'];
        $foreignKey = $params['settings']['foreign_key'];
        $relation = $params['settings']['relation'];
        $modelTable = str_replace($prefix,'',$tableName);
        $assField = $this->getTableField($relationTable,$localKey);
        if(empty($assField)){
            return ['code'=>-13,'msg'=>'关联字段不存在,请稍后重试'];
        }else{
            $assField = $assField[0];
        }
        $assOption = $this->getAssFieldOption($assField);
        $assOption['column_name'] = $foreignKey;
        if($relation == 'belongs_to'){
            if($after = $params['order']){
                $assOption['after'] = "AFTER `$after`";
            }else{
                $assOption['after'] = '';
            }
            $this->alterColumn($tableName,$assOption);
        }

        if($relation == 'has_many'){
            if($after = $params['order']){
                $assOption['after'] = "AFTER `$after`";
            }else{
                $assOption['after'] = '';
            }
            $assOption['null'] = '';
            $assOption['type'] = 'varchar';
            $assOption['length'] = '255';
            $this->alterColumn($tableName,$assOption);
        }

        if($relation == 'belongs_to_many') {
            // 模型文件
            $modField = $this->getTableField($tableName,$foreignKey);
            if(empty($modField)){
                return ['code'=>-12,'msg'=>'被关联字段不存在,请稍后重试'];
            }else{
                $modField = $modField[0];
            }
            $modOption = $this->getAssFieldOption($modField);
            $modelField = $modelTable .'_id';
            $relationField = $params['settings']['table'] . '_' . $params['settings']['foreign_key'];
            $povitTable = $tableName . '_' . $params['settings']['table'];
            if ($this->hasTable($povitTable)) {
                return 'success';
            }
            // 建立关联表和字段
            $sql = sprintf("
            CREATE TABLE %s (
                    `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
                    `%s` %s%s %s %s %s,
                    `%s` %s%s %s %s %s,
                    PRIMARY KEY(`id`) 
                )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ", $povitTable,
                $modelField, $modField['DATA_TYPE'], $modOption['length'], $modOption['default'], $modOption['null'], $modOption['comment'],
                $relationField, $assField['DATA_TYPE'], $assOption['length'], $assOption['default'], $assOption['null'], $assOption['comment']);

            Db::execute($sql);
        }
        return 'success';
    }

    /**
     * 修改字段
     *
     * @param $table
     * @param $params
     * @return mixed
     */
    public function alterColumn($table,$params)
    {
        if(!empty($params['length'])){
            $params['length'] = '('.$params['length'].')';
        }
        $sql = sprintf(
            "ALTER TABLE %s Add %s %s%s %s %s %s;",$table,$params['column_name'],$params['type'],$params['length'],$params['null'],$params['comment'],$params['after']
        );
        return Db::execute($sql);
    }

    /**
     * Drop model table
     *
     * @param string $name
     * @throws \Exception
     */
    public function dropTable(string $name): bool
    {
        $has_table = $this->hasTable($name);
        if(!$has_table){
            throw new \Exception("table [$name] is not exists!");
        }
        $drop_sql = "DROP TABLE $name";
        $res = Db::execute($drop_sql);
        return empty($res);
    }

    /** 
     * Has Table
     *
     * @param string $name
     * @return boolean
     */
    public function hasTable(string $name): bool
    {
        $database = env('database.database');
        $exists = Db::query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = :database AND TABLE_NAME = :name ", 
        ['database' => $database,'name' => $name]);
        return !empty($exists);
    }

    /**
     * 获取多对多关联表字段配置
     *
     * @param array $assField
     * @return array $assOption
     */
    public function getAssFieldOption(array $assField): array
    {
        $assOption = [];
        $assOption['type'] = $assField['DATA_TYPE'];

        if($assField['CHARACTER_MAXIMUM_LENGTH']){
            $assOption['length'] = '('.$assField['CHARACTER_MAXIMUM_LENGTH'].')';
        }else{
            $assOption['length'] = '';
        }
        if($assField['COLUMN_DEFAULT']){
            if(is_int($assField['COLUMN_DEFAULT']) || is_float($assField['COLUMN_DEFAULT'])){
                $assOption['default'] = 'DEFAULT '.$assField['COLUMN_COMMENT'];
            }else{
                $assOption['default'] = 'DEFAULT '.'\''.$assField['COLUMN_COMMENT'].'\'';
            }
        }else{
            $assOption['default'] = '';
        }
        if($assField['IS_NULLABLE'] == 'YES'){
            $assOption['null'] = '';
        }else{
            $assOption['null'] = 'NOT NULL';
        }
        if($assField['COLUMN_COMMENT']){
            $assOption['comment'] = 'COMMENT '.'\''.$assField['COLUMN_COMMENT'].'\'';
        }else{
            $assOption['comment'] = '';
        }
        return $assOption;
    }

    /**
     * 更新字段
     *
     * @param array $params
     * @return array
     * @throws ModelEmptyException
     * @throws \app\exception\ModelException
     */
    public function update(array $params):array
    {
        $id = $params['id'];
        $moduleField = new ModuleField();
        $field = $moduleField->getModuleField(['id' => $params['id'], 'seller_id' => $params['seller_id']])['data']->toArray();
        if($field['module']['is_system'] == 1){
            return dataReturn(50029,'关联字段不能编辑');
        }
        if($field['type'] == '关联字段'){
            return dataReturn(50027,'关联字段不能编辑');
        }
        $tableName = $field['module']['database_table'];
        $columnName = $field['table_field'];
        $res = $this->uniqueValidate($field['module_id'],$params,true,$id);
        $orderFlag = $this->hasOrderField($params['order'],$field['module_id']);
        if(!$orderFlag){
            $params['order'] = 'id';
        }
        if($res['code'] != 0){
            return $res;
        }
        if(!in_array($params['type'],$this->fieldType['dataTypes'])){
            return dataReturn(50024,'字段类型不存在');
        }
        $params = $this->setParams($params);
        $moduleField = new ModuleField();
        Db::startTrans();
        try{
            $res = $this->hasOrder($params['order'],$field['module_id'],$params['id']);
            if(isset($res['id'])){
                $field_id = $res['id'];
                $moduleField->updateModuleField(['id' => $field_id,'seller_id' => $params['seller_id']],['order' => $params['table_field']]);
            }
            $option = $this->getFieldOption($params);
            $this->renameColumn($tableName,$columnName,$params['table_field']);
            $this->changeColumn($tableName,$params['table_field'],$params['db_type'],$option);
            $updateRes = $moduleField->updateModuleField(['id' => $params['id'],'seller_id' => $params['seller_id']],$params);
            Db::commit();
        }catch(\Exception $e){
            $res = $this->hasColumn($tableName,$params['table_field']);
            if($res){
                $this->renameColumn($tableName,$params['table_field'],$columnName);
                $this->changeColumn($tableName,$columnName,$this->getFieldOption($field));
            }
            Db::rollback();
        }
        return $updateRes;
    }

    /**
     * 删除字段
     *
     * @param array $param
     * @return array $res
     * @throws ModelEmptyException
     * @throws \app\exception\ModelException
     */
    public function destroy(array $param): array
    {
        $moduleField = new ModuleField();
        $field = $moduleField->getModuleField($param)['data']->toArray();

        $tableName = $field['module']['database_table'];
        $columnName = $field['table_field'];
        Db::startTrans();
        try {
            if($field['type'] == '关联字段' && $field['settings']['relation'] == 'belongs_to_many'){
                // 关联表名称
                $middle = $field['module']['database_table'] .'_'. str_replace(env('database.prefix'),'',$field['settings']['table']);
                $tableSql = $this->getTableCreateRow($middle);
                // 删除关联表
                $this->dropTable($middle);
            }else{
                $beOrder = $moduleField->getOnlyModuleField(['module_id'=>$param['module_id'],'order' => $field['table_field']])['data'];
                if(!empty($beOrder)){
                    $beOrder->order = $field['order'];
                    $beOrder->save();
                }
                $this->dropColumn($tableName,$columnName);

            }
            $res = $moduleField->delModuleField($param);
            Db::commit();
        } catch (\Exception $e) {
            if($field['type'] == '关联字段' && $field['settings']['relation'] == 'belongs_to_many'){
                // 建立关联中间表
                if(!empty($tableSql)){
                    $tableSql = $tableSql[0]['Create Table'];
                    Db::execute($tableSql);
                }
            }else{
                $res = $this->hasColumn($tableName,$columnName);
                if(!$res){
                    $this->addColumn($tableName,$columnName,$field['db_type'],$this->getFieldOption($field));
                }
            }

            Db::rollback();
            return dataReturn(50025,$e->getMessage());
        }
        return dataReturn(0,'删除成功');

    }

    public function dealWithComma($param)
    {
        return preg_replace('/，/',',',$param);
    }

    /**
     * 判断排序字段是否存在
     *
     * @param string $order
     * @param int $moduleId
     * @param int $fieldId
     * @return bool
     */
    public function hasOrderField(string $order, int $moduleId,int $fieldId = 0): bool
    {
        $state = false;

        $field = ModuleField::field(['id','module_id','table_field','order'])->where(['module_id' =>$moduleId,'table_field' => $order])->find();

        if(!empty($field)){
            $state = true;
        }
        return $state;
    }

    /**
     * 判断模型字段表中排序
     *
     * @param string $order
     * @param int $id
     * @return array|boolean
     * @throws ModelEmptyException
     */
    public function hasOrder(string $order, int $moduleId,int $fieldId = 0)
    {

        $field = ModuleField::field(['id','module_id','table_field','order'])->where(['module_id' =>$moduleId,'order' => $order])->find();

        if(!empty($field) && $field['id'] != $fieldId){
            return ['id'=>$field['id']];
        }
        return false;

    }

    /**
     * 字段名称和字段值唯一性认证
     *
     * @param integer $module_id
     * @param array $params
     * @param boolean $is_edit
     * @param integer $field_id
     * @return array
     */
    public function uniqueValidate(int $module_id, array $params, bool $is_edit = true, int $field_id = 0): array
    {
        $fields = ModuleField::field(['id','form_title','table_field'])->where('module_id',$module_id)->select();
        if(empty($fields)){
            return dataReturn(0,'success');
        }
        foreach($fields as $val){
            if($is_edit){
                if($params['form_title'] == $val['form_title'] && $field_id != $val['id']){
                    return dataReturn(50020,'字段名称已存在');
                }
            }else{
                if($params['form_title'] == $val['form_title']){
                    return dataReturn(50021,'字段名称已存在');
                }
            }
        }
        return dataReturn(0,'success');
    }

    /**
     * 获取表名
     *
     * @param int $id
     */
    public function getTableModule(int $id,int $sellerId = 1)
    {
        $module = new Module();
        return $module->getModule(['id' => $id, 'seller_id' => $sellerId])['data'];
    }

    /**
     * 获取字段属性
     *
     * @param array $params
     */
    public function getFieldOption(array $params): array
    {
        $option = [];
        if(isset($params['length']) && is_int($params['length']) && $params['type'] != "富文本编辑器"){
            $option['limit'] = $params['length'];
        }
        if(isset($params['default']) && ($params['db_type'] == 'char' || $params['db_type'] == 'string' || $params['db_type'] == 'int' )){
            $params['default'] ? $option['default'] = $params['default'] : $option['default']= '';
        }
        // 允许字段为空
        $option['null'] = true;
        if(isset($params['form_title'])){
            $option['comment'] = $params['form_title'];
        }
        if(isset($params['order'])){
            $option['after'] = $params['order'];
        }

        return $option;
    }

    /**
     * 判断字段是否存在
     *
     * @param string $tableName
     * @param string $columnName
     * @return boolean
     */
    public function hasColumn(string $tableName, string $columnName): bool
    {
        return $this->table->table($tableName)->hasColumn($columnName);
    }

    /**
     * 添加字段
     *
     * @param string $tableName
     * @param string $columnName
     * @param string|null $type
     * @param array $option
     */
    public function addColumn(string $tableName, string $columnName, string $type=null, array $option=[])
    {
        return $this->table->table($tableName)->addColumn($columnName,$type,$option)->save();
    }

    public function getTable()
    {
        return $this->table->getTableNameAndComment();
    }

    /**
     * 获取数据表创建sql语句
     *
     * @param string $tableName
     * @return mixed
     */
    public function getTableCreateRow(string $tableName)
    {
        $sql = sprintf("show create table %s",$tableName);
        return Db::query($sql);
    }

    /**
     * 获取字段值和注释
     *
     * @param string $table
     * @return array
     */
    public function getTableField(string $table,$column=null): array
    {
        return $this->table->getTableField($table,$column);
    }

    /**
     * 字段删除
     *
     * @param string $tableName
     * @param string $columnName
     */
    public function dropColumn(string $tableName, string $columnName)
    {
        return $this->table->table($tableName)->removeColumn($columnName)->save();
    }

    /**
     * 字段重命名
     *
     * @return void
     */
    public function renameColumn($tableName,$oldName,$newName)
    {
        return $this->table->table($tableName)->renameColumn($oldName,$newName)->save();
    }

    /**
     * 修改字段属性
     *
     * @param string $tableName
     * @param string $columnName
     * @param string|null $columnType
     * @param array $option
     */
    public function changeColumn(string $tableName, string $columnName, string $columnType=null, array $option=[])
    {
        return $this->table->table($tableName)->changeColumn($columnName,$columnType,$option)->save();
    }

    /**
     * @throws \app\exception\ModelException
     * @throws ModelEmptyException
     * @throws \think\db\exception\DbException
     */
    public function resotreData(int $id, $sellerId)
    {
        $recycleBin = new RecycleBin();
        $where = [
            'id' => $id
        ];
        Db::startTrans();
        try{
            $binData = $recycleBin->getRecycleBin($where)['data'];
            if($binData['module_id']){
                // 存在副表
                // 副表数据恢复
                $subContent = new SubContent();
                $subContent-> updateSubContent(['id'=>$binData['sub_id'],'seller_id'=>$sellerId],['is_del'=>1,'delete_time'=>0]);
            }
            // 回收站文件删除
            $res = $recycleBin->delRecycleBin($where);
            if($this->hasColumn(env('database.prefix').$binData['table_name'],'is_del')){
                // 主表数据恢复
                 Db::name($binData['table_name'])->where(['id'=>$binData['object_id']])->update(['is_del'=>1,'delete_time'=>0]);
            }
            Db::commit();
        }catch (\Exception $e){
            Db::rollback();
            return jsonReturn(-3,$e->getMessage());
        }

        return jsonReturn(0,'恢复成功',$res);
    }

}